﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GBMS.MODEL;
using KPIS.GERP.GBMS.DAL;

namespace KPIS.GERP.GBMS.DAL
{
    public sealed class SourceDAL : ISourceDAL
    {
        private IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        private ConvertNullable cn = new ConvertNullable();

        public SourceDAL()
        {

        }

        public IList<TreeViewInfo> TreeView(IList<TreeViewInfo> treeView, bool isRootNode)
        {
            string strSQL = "SELECT CONCAT('SOURCE-', S.BGM_SOURCE_SEQ) AS ID, S.BGM_SOURCE_NAME AS NAME"
                            +
                            ((isRootNode)
                                 ? ", CONCAT('SOURCE-', S.UPPER_BGM_SOURCE_SEQ)"
                                 : ", COALESCE(CONCAT('SOURCE-', S.UPPER_BGM_SOURCE_SEQ), CONCAT('SOURCETYPE-', S.BGM_SOURCE_TYPE_SEQ))") +
                            " AS PARENT"
                            + ", S.BGM_SOURCE_NAME AS TEXT_FIELD, CONCAT('SOURCE-', S.BGM_SOURCE_SEQ) AS VALUE_FIELD"
                            + " FROM BGM_MAS_SOURCE S"
                            + " WHERE S.RECORD_STATUS not like @status";

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@status", SystemInfo._DeleteRecordStatus);

                DataTable dtb = dbManager.ExecuteDataTable(CommandType.Text, strSQL);

                foreach (DataRow dr in dtb.Rows)
                {
                    TreeViewInfo source = new TreeViewInfo();

                    source.ID = Convert.ToString(dr["ID"]);
                    source.Name = Convert.ToString(dr["NAME"]);
                    source.Parent = Convert.ToString(dr["PARENT"]);
                    source.TextField = Convert.ToString(dr["TEXT_FIELD"]);
                    source.ValueField = Convert.ToString(dr["VALUE_FIELD"]);

                    treeView.Add(source);
                }

                //while (rdr.Read())
                //{
                //    TreeViewInfo source = new TreeViewInfo(rdr.GetString(0), rdr.GetString(1),
                //                                           (rdr.IsDBNull(2)) ? null : rdr.GetString(2), rdr.GetString(3),
                //                                           rdr.GetString(4));
                //    treeView.Add(source);
                //}

                return treeView;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public int Insert(SourceInfo srcInfo)
        {
            string strSQL;
            Nullable<int> parent = null;

            if (srcInfo.Parent.Sequence > 0)
                parent = srcInfo.Parent.Sequence;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(8);

                dbManager.AddParameters(0, "@code", srcInfo.Code);
                dbManager.AddParameters(1, "@name", srcInfo.Name);
                dbManager.AddParameters(2, "@descr", srcInfo.Description);
                dbManager.AddParameters(3, "@sourceParent", parent);
                dbManager.AddParameters(4, "@SourceType", srcInfo.Type.Sequence);
                dbManager.AddParameters(5, "@status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(6, "@user", null);
                dbManager.AddParameters(7, "@gfmis_code", srcInfo.Gfmis_code);
       
                strSQL = "INSERT INTO BGM_MAS_SOURCE (BGM_SOURCE_CODE, BGM_SOURCE_NAME, BGM_SOURCE_DESCR"
                         + ", UPPER_BGM_SOURCE_SEQ, BGM_SOURCE_TYPE_SEQ, RECORD_STATUS, gfmis_code)"
                         + " VALUES (@code, @name, @descr, @sourceParent, @SourceType, @status, @gfmis_code) ";

                return dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public int Update(SourceInfo srcInfo)
        {
            string strSQL;
            Nullable<int> parent = null;

            if (srcInfo.Parent.Sequence > 0)
                parent = srcInfo.Parent.Sequence;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(8);

                dbManager.AddParameters(0, "@seq", srcInfo.Sequence);
                dbManager.AddParameters(1, "@code", srcInfo.Code);
                dbManager.AddParameters(2, "@name", srcInfo.Name);
                dbManager.AddParameters(3, "@descr", srcInfo.Description);
                dbManager.AddParameters(4, "@sourceParent", parent);
                dbManager.AddParameters(5, "@SourceType", srcInfo.Type.Sequence);
                dbManager.AddParameters(6, "@status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(7, "@gfmis_code", srcInfo.Gfmis_code);
 
                strSQL = "UPDATE BGM_MAS_SOURCE"
                         + " SET BGM_SOURCE_CODE = @code"
                         + ", BGM_SOURCE_NAME = @name"
                         + ", BGM_SOURCE_DESCR = @descr"
                         + ", UPPER_BGM_SOURCE_SEQ = @sourceParent"
                         + ", BGM_SOURCE_TYPE_SEQ = @SourceType"
                         + ", RECORD_STATUS = @status"
                         + ", gfmis_code = @gfmis_code"
                         + " WHERE BGM_SOURCE_SEQ = @seq";

                return dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public int Delete(int seq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@seq", seq);
                dbManager.AddParameters(1, "@status", SystemInfo._DeleteRecordStatus);

                return dbManager.ExecuteNonQuery(CommandType.Text,
                                                 "UPDATE BGM_MAS_SOURCE SET RECORD_STATUS = @status WHERE BGM_SOURCE_SEQ = @seq");
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public IList<SourceInfo> Search(SourceInfo srcInfo)
        {
            string strSQL;
            int parent = 0;
            int type = 0;

            if (srcInfo.Parent != null)
                parent = srcInfo.Parent.Sequence;

            if (srcInfo.Type != null)
                type = srcInfo.Type.Sequence;

            List<SourceInfo> srcList = new List<SourceInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(7);

                dbManager.AddParameters(0, "@seq", srcInfo.Sequence);
                dbManager.AddParameters(1, "@code", srcInfo.Code);
                dbManager.AddParameters(2, "@name", srcInfo.Name);
                dbManager.AddParameters(3, "@descr", srcInfo.Description);
                dbManager.AddParameters(4, "@status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(5, "@parent", parent);
                dbManager.AddParameters(6, "@type", type);


                strSQL = "SELECT S.BGM_SOURCE_SEQ, S.BGM_SOURCE_CODE, S.BGM_SOURCE_NAME, S.BGM_SOURCE_DESCR"
                         + ", S.RECORD_STATUS, S.UPPER_BGM_SOURCE_SEQ, S.BGM_SOURCE_TYPE_SEQ, s.gfmis_code"
                         + " FROM BGM_MAS_SOURCE S"
                         + " WHERE S.RECORD_STATUS not like @status"
                         + ((srcInfo.Sequence > 0) ? " AND S.BGM_SOURCE_SEQ = @seq" : "")
                         + ((srcInfo.Code.Length > 0) ? " AND S.BGM_SOURCE_CODE = @code" : "")
                         + ((srcInfo.Name.Length > 0) ? " AND S.BGM_SOURCE_NAME = @name" : "")
                         + ((srcInfo.Description.Length > 0) ? " AND S.BGM_SOURCE_DESCR = @descr" : "")
                         + ((parent > 0) ? " AND S.UPPER_BGM_SOURCE_SEQ = @parent" : "")
                         + ((type > 0) ? " AND S.BGM_SOURCE_TYPE_SEQ = @type" : "");


                DataTable dtb = dbManager.ExecuteDataTable(CommandType.Text, strSQL);

                foreach (DataRow dr in dtb.Rows)
                {
                    SourceInfo srcParentInfo = new SourceInfo();

                    if (dr["UPPER_BGM_SOURCE_SEQ"] == DBNull.Value)
                        srcParentInfo = null;
                    else srcParentInfo.Sequence = Convert.ToInt32(dr["UPPER_BGM_SOURCE_SEQ"]);

                    SourceTypeDAL srcTypeCtrl = new SourceTypeDAL();
                    IList<SourceTypeInfo> srcTypeList =
                        srcTypeCtrl.Search(new SourceTypeInfo(Convert.ToInt32(dr["BGM_SOURCE_TYPE_SEQ"]), "", "", "", "", null));

                    SourceInfo src = new SourceInfo();
                    src.Sequence = Convert.ToInt32(dr["BGM_SOURCE_SEQ"]);
                    src.Code = (dr["BGM_SOURCE_CODE"]).ToString();
                    src.Name = (dr["BGM_SOURCE_NAME"]).ToString();
                    src.Description = (dr["BGM_SOURCE_DESCR"]).ToString();
                    src.Gfmis_code = (dr["gfmis_code"]).ToString();
                    src.Type = new SourceTypeInfo(Convert.ToInt32(dr["BGM_SOURCE_SEQ"]), (dr["BGM_SOURCE_CODE"]).ToString(), (dr["BGM_SOURCE_NAME"]).ToString(), (dr["BGM_SOURCE_DESCR"]).ToString(), (dr["RECORD_STATUS"]).ToString()
                                                 , null);
                    src.Parent = srcParentInfo;


                    srcList.Add(src);
                }

                //while (rdr.Read())
                //{
                //    SourceInfo srcParentInfo = new SourceInfo();

                //    if (rdr.IsDBNull(5))
                //        srcParentInfo = null;
                //    else srcParentInfo.Sequence = rdr.GetInt32(5);

                //    SourceTypeDAL srcTypeCtrl = new SourceTypeDAL();
                //    IList<SourceTypeInfo> srcTypeList =
                //        srcTypeCtrl.Search(new SourceTypeInfo(rdr.GetInt32(6), "", "", "", "", null));

                //    SourceInfo src = new SourceInfo(rdr.GetInt32(0)
                //                                    , (rdr.IsDBNull(1)) ? "" : rdr.GetString(1)
                //                                    , (rdr.IsDBNull(2)) ? "" : rdr.GetString(2)
                //                                    , (rdr.IsDBNull(3)) ? "" : rdr.GetString(3)
                //                                    , rdr.GetString(4)
                //                                    , (rdr.IsDBNull(7)) ? "" : rdr.GetString(7)
                //                                    ,
                //                                    new SourceTypeInfo(srcTypeList[0].Sequence, srcTypeList[0].Code,
                //                                                       srcTypeList[0].Name, srcTypeList[0].Description,
                //                                                       srcTypeList[0].Status, null)
                //                                    , srcParentInfo, null);

                //    srcList.Add(src);
                //}

                return srcList;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public bool CheckDuplicateCode(string code, string currentSeq)
        {
            string strSQL;

            bool result = false;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@deletestatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_source_code", code);
                dbManager.AddParameters(2, "@bgm_source_seq", currentSeq);

                strSQL = @"select bgm_source_code 
                        from BGM_MAS_SOURCE 
                        where bgm_source_code = @bgm_source_code "
                         + (String.IsNullOrEmpty(currentSeq)
                                ? String.Empty
                                : " AND bgm_source_seq not like @bgm_source_seq")
                         + " AND record_status not like @deletestatus";

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    result = true; // found duplicate
                    break;
                }
            }
            catch (Exception error)
            {
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }

            return result;
        }
    }
}